package logic;

import shared.*;
import com.smartxls.*;

public class Excel {
	
	
	public static void export(API api) throws Exception{
		int[] categorySum = new int[api.getCategoryList().size()], modeSum = new int[api.getModeList().size()];
		int incomeSum = 0, expenseSum = 0;
		
		WorkBook workbook = new WorkBook();
		
		//Export to 4 different sheets: Record/Loans/Receipts/Summary
		workbook.setNumSheets(4);
		workbook.setSheetName(0, "Account");
		workbook.setSheetName(1, "Loan");
		workbook.setSheetName(2, "Receipt");
		workbook.setSheetName(3, "Summary");
		
		//First Cell
		workbook.setText(0,0, "Account Balance");
		
		//ID??
		workbook.setText(1,0, "Name");
		workbook.setText(1,1, "Date");
		workbook.setText(1,2, "Income");
		workbook.setText(1,3, "Expense");
		workbook.setText(1,4, "Balance");
		workbook.setText(1,5, "Payment Mode");
		workbook.setText(1,6, "Category");
		workbook.setText(1,7, "Description");
		RangeStyle boldStyle = workbook.getRangeStyle(1, 0, 1, 6);
		boldStyle.setFontBold(true);
		workbook.setRangeStyle(boldStyle, 1, 0, 1, 6);
		
		Record[] recordArray = api.getDateArray();
		int row = 1 + recordArray.length, balance = api.getBalance();
		for(Record record:recordArray){
			workbook.setText(row,0, record.getName());
			workbook.setEntry(row,1, record.getMonth()+"/"+record.getDay()+"/"+record.getYear());
			workbook.setNumber(row,4, balance/100.0);
			if(record.getClass()==Income.class){
				workbook.setNumber(row,2, (record.getAmt()/100.0));
				balance -= record.getAmt();
				incomeSum += record.getAmt();
			}
			else{
				workbook.setNumber(row,3, (record.getAmt()/100.0));
				workbook.setText(row,5, ((Expense)record).getMode().getName());
				workbook.setText(row,6, ((Expense)record).getCat().getName());
				balance += record.getAmt();
				categorySum[api.getCategoryList().indexOf(((Expense)record).getCat())]+=record.getAmt();
				modeSum[api.getModeList().indexOf(((Expense)record).getMode())]+=record.getAmt();
			}
			workbook.setText(row,7, record.getDetail());
			row--;
			
		}
		//Formatting
		RangeStyle cashStyle = workbook.getRangeStyle(2, 2, 2, 2);
		cashStyle.setCustomFormat("$#,###.00;$-#,###.00;$0.00;\"Error: Entry must be numeric\"");
		workbook.setRangeStyle(cashStyle, 2, 2, recordArray.length + 2, 4);
		RangeStyle dateStyle = workbook.getRangeStyle(2,1,2,1);
		dateStyle.setCustomFormat("dd/mm/yyyy");
		workbook.setRangeStyle(dateStyle, 2, 1, recordArray.length + 2, 1);
		for(int i=0;i<7;i++)
			workbook.setColWidthAutoSize(i,true);
		//End Formatting
		
		//Summary Sheet
		workbook.setSheet(3);
		workbook.setText(0,0, "Summary");

		workbook.setText(1,0, "Category");
		workbook.setText(1,1, "Amount");
		workbook.setText(1,3, "Payment Mode");
		workbook.setText(1,4, "Amount");
		//Formatting
		workbook.setRangeStyle(boldStyle, 1, 0, 1, 4);
		
		for(int i=0;i<categorySum.length;i++){
			workbook.setText(2 + i, 0, api.getCategoryList().get(i).getName());
			workbook.setNumber(2 + i, 1, categorySum[i]/100.0);
			expenseSum += categorySum[i];
		}
		for(int i=0;i<modeSum.length;i++){
			workbook.setText(2 + i, 3, api.getModeList().get(i).getName());
			workbook.setNumber(2 + i, 4, modeSum[i]/100.0);
		}
		workbook.setText(2 + categorySum.length, 0, "Total Expenses");
		workbook.setText(3 + categorySum.length, 0, "Total Income");
		workbook.setNumber(2 + categorySum.length, 1, expenseSum/100.0);
		workbook.setNumber(3 + categorySum.length, 1, incomeSum/100.0);
		
		workbook.setRangeStyle(boldStyle, 2 + categorySum.length, 0, 3 + categorySum.length, 0);
		workbook.setRangeStyle(cashStyle, 2, 1, 1+categorySum.length, 1);
		workbook.setRangeStyle(cashStyle, 2, 4, 3+categorySum.length, 4);
		
		ChartShape chart = workbook.addChart(0, 5 + categorySum.length, 5, 30 + categorySum.length);
		chart.setChartType(ChartShape.Pie);
		//link data source, link each series to columns(true to rows).
		chart.setLinkRange("Summary!$A$2:$B$"+(2+categorySum.length), false);
		
		chart = workbook.addChart(7, 5 + categorySum.length, 12, 30 + categorySum.length);
		chart.setChartType(ChartShape.Pie);
		//link data source, link each series to columns(true to rows).
		chart.setLinkRange("Summary!$D$2:$E$"+(2+categorySum.length), false);
		//End Summary Sheet

		//Loan Sheet
		workbook.setSheet(1);
		//First Cell
		workbook.setText(0,0, "Loans");
		
		//ID??
		workbook.setText(1,0, "Name");
		workbook.setText(1,1, "Date");
		workbook.setText(1,2, "Amount");
		workbook.setText(1,3, "Category");
		workbook.setText(1,4, "Cash Borrowed");
		workbook.setText(1,5, "Lender");
		workbook.setText(1,6, "Cleared");
		workbook.setText(1,7, "Description");
		workbook.setRangeStyle(boldStyle, 1, 0, 1, 7);
		
		row = 2;
		for(Loan loan:api.getLoanList()){
			//loan record
			workbook.setText(row,0, loan.getName());
			workbook.setEntry(row,1, loan.getMonth()+"/"+loan.getDay()+"/"+loan.getYear());
			workbook.setNumber(row,2, (loan.getAmt()/100.0));
			if(loan.getCat().getName().equals(API.CashLoan))
				workbook.setText(row,3, "Cash Loan");
			else
				workbook.setText(row,3, loan.getCat().getName());
			workbook.setNumber(row,4, (loan.getIniAmt()/100.0));
			workbook.setText(row,5, loan.getLender());
			workbook.setEntry(row,6, loan.isClear()?"True":"False");
			workbook.setText(row,7, loan.getDetail());
			//End loan record
			//formatting
			workbook.setRangeStyle(dateStyle, row, 1, row, 1);
			workbook.setRangeStyle(cashStyle, row, 2, row, 2);
			workbook.setRangeStyle(cashStyle, row, 4, row, 4);
			row++;
			//End formatting
			
			workbook.setText(row,0, "Installments");
			workbook.setText(row,1, "Date");
			workbook.setText(row,2, "Amount");
			workbook.setText(row,3, "Payment mode");
			workbook.setText(row,4, "Description");
			workbook.setText(row,5, "Future Payment");
			workbook.setText(row,6, "Date");
			workbook.setText(row,7, "Amount");
			workbook.setRangeStyle(boldStyle, row, 0, row, 7);
			
			row++;
			
			int installmentRow = row;
			for(Expense installment:loan.getInstallments()){
				//Installment Record
				workbook.setEntry(installmentRow, 1, installment.getMonth()+"/"+installment.getDay()+"/"+installment.getYear());
				workbook.setNumber(installmentRow, 2, installment.getAmt()/100.0);
				workbook.setText(installmentRow, 3, installment.getMode().getName());
				workbook.setText(installmentRow, 4, installment.getDetail());
				//End Installment
				installmentRow++;
			}
			//Formatting
			workbook.setRangeStyle(dateStyle, row, 1, installmentRow, 1);
			workbook.setRangeStyle(cashStyle, row, 2, installmentRow, 2);
			//End Formatting
			
			int paymentRow = row;
			for(Payment payment:loan.getFuturePayments()){
				//Payment record
				workbook.setEntry(paymentRow, 6, payment.getMonth()+"/"+payment.getDay()+"/"+payment.getYear());
				workbook.setNumber(paymentRow, 7, payment.getAmt()/100.0);
				//End pament
				paymentRow++;
			}
			//Formatting
			workbook.setRangeStyle(dateStyle, row, 1, installmentRow, 1);
			workbook.setRangeStyle(cashStyle, row, 2, installmentRow, 2);
			//End Formatting
			
			row = installmentRow>paymentRow ? installmentRow + 1 : paymentRow + 1;
		}
		
		
		/*
		ChartShape chart = workbook.addChart(10, 0, 15, 25);
		chart.set3Dimensional(true);
		chart.setChartType(ChartShape.Column);
		//link data source, link each series to columns(true to rows).
		chart.setLinkRange("Account!$A$2:$A$11,Account!$C$2:$D$11", false);
		//set axis title
		chart.setAxisTitle(ChartShape.XAxis, 0, "Item");
		chart.setAxisTitle(ChartShape.YAxis, 0, "Amount");
		//set series name
		//chart.setSeriesName(0, "My Series number 1");
		//chart.setSeriesName(1, "My Series number 2");
		chart.setTitle("Banana");
		//set plot area's color to darkgray
		ChartFormat chartFormat = chart.getPlotFormat();
		//chartFormat.setSolid();
		//chartFormat.setForeColor(Color.red.getRGB());
		//chart.setPlotFormat(chartFormat);

		//set series 0's color to blue
		//ChartFormat seriesformat = chart.getSeriesFormat(0);
		//seriesformat.setSolid();
		//seriesformat.setForeColor(Color.BLUE.getRGB());
		//chart.setSeriesFormat(0, seriesformat);


		//set series 1's color to red
		//seriesformat = chart.getSeriesFormat(1);
		//seriesformat.setSolid();
		//seriesformat.setForeColor(Color.RED.getRGB());
		//chart.setSeriesFormat(1, seriesformat);

		//set chart title's font property
		//ChartFormat titleformat = chart.getTitleFormat();
		//titleformat.setFontSize(14*20);
		//titleformat.setFontUnderline(true);
		//titleformat.setTextRotation(90);
		//chart.setTitleFormat(titleformat);
		*/
		
		workbook.writeXLSX("data.xlsx");
	}
	
	public static void load(API api) throws Exception{
		//Previous Data will be lost Continue?
		
		api.reset();
		add(api);
		
	}
	
	public static void add(API api) throws Exception{
		
		WorkBook workbook = new WorkBook();
		workbook.readXLSX("data.xlsx");
		int row;

		//Read Records
		workbook.setSheet(0);
		for(row = 2;workbook.getType(row, 0) != WorkBook.TypeEmpty; row++){
			String name = workbook.getText(row, 0);
			String date = workbook.getFormattedText(row, 1);
			//
			System.out.println(date);
			//
			if(!date.matches("\\d*/\\d*/\\d*")){
				System.out.println("Invalid date format for Record at B"+(row+1));
				throw new Exception("Invalid date format for Record at B"+(row+1));
			}
			String[] split = date.split("/");
			int day = Integer.valueOf(split[0]), month = Integer.valueOf(split[1]), year = Integer.valueOf(split[2]);
			String description = workbook.getText(row, 7);
			
			int amount;
			if(workbook.getType(row, 2)==WorkBook.TypeNumber){
				amount = (int) (workbook.getNumber(row, 2)*100 +0.0001);
				
				api.addIncome(name, amount, day, month, year, description);
			}else if(workbook.getType(row, 3)==WorkBook.TypeNumber){
				amount = (int) (workbook.getNumber(row, 3)*100 +0.0001);
				String modeName = workbook.getText(row, 5);
				Mode mode = null;
				for(Mode current:api.getModeList())
					if(modeName.equals(current.getName()))
						mode = current;
				if(mode == null)
					mode = api.addMode(modeName);
				String catName = workbook.getText(row, 6);
				Category category = null;
				for(Category current:api.getCategoryList())
					if(catName.equals(current.getName()))
						category = current;
				if(category == null)
					category = api.addCategory(catName);
				
				api.addExpense(name, amount, day, month, year, description, mode, category);
			}else{
				System.out.println("Record has no amount at C/D"+(row+1));
				throw new Exception("Record has no amount at C/D"+(row+1));
			}
			
		}
		//End Records
		
		//Read Loans
		workbook.setSheet(1);
		row = 2;
		//Loop until no more loans
		while(!(workbook.getType()==WorkBook.TypeEmpty)){

			String name = workbook.getText(row, 0);
			String date = workbook.getFormattedText(row, 1);
			//
			System.out.println(date);
			//
			if(!date.matches("\\d*/\\d*/\\d*")){
				System.out.println("Invalid date format for Loan at B"+(row+1));
				throw new Exception("Invalid date format for Loan at B"+(row+1));
			}
			String[] split = date.split("/");
			int day = Integer.valueOf(split[0]), month = Integer.valueOf(split[1]), year = Integer.valueOf(split[2]);
			int amount = (int) (workbook.getNumber(row, 2)*100 +0.0001);
			String catName = workbook.getText(row, 3);
			Category category = null;
			int initial = (int) (workbook.getNumber(row, 4)*100 +0.0001);
			String lender = workbook.getText(row, 5);
			String description = workbook.getText(row, 7);
			Loan loan;
			if(catName.equals("Cash Loan")||catName.equals(API.CashLoan))
				loan = api.addCashLoan(name, amount, day, month, year, description, initial, lender);
			else{
				for(Category current:api.getCategoryList())
					if(catName.equals(current.getName()))
						category = current;
				if(category == null)
					category = api.addCategory(catName);
				loan = api.addItemLoan(name, amount, day, month, year, description, initial, category, lender);
			}
			if(workbook.getType(row, 6) != WorkBook.TypeLogical && workbook.getType(row, 6) != WorkBook.TypeEmpty){
				System.out.println("Expecting TRUE/FALSE/empty cell for Loan at G"+(row+1));
				throw new Exception("Expecting TRUE/FALSE/empty cell for Loan at G"+(row+1));
			}
			if(workbook.getText(row, 6).equals("TRUE"))
				loan.clear();
			
			row++;
			//Check for Installment/Future Payment Heading
			if(workbook.getType(row, 1) != WorkBook.TypeEmpty)
				if(workbook.getType(row, 0) == WorkBook.TypeEmpty)
					row++;
			int installmentRow = row, paymentRow = row;
				
				//Loop until no more installments
			while(workbook.getType(installmentRow, 1) != WorkBook.TypeEmpty){
				date = workbook.getFormattedText(installmentRow, 1);
				//
				System.out.println(date);
				//
				if(!date.matches("\\d*/\\d*/\\d*")){
					System.out.println("Invalid date format for Loan at B"+(installmentRow+1));
					throw new Exception("Invalid date format for Loan at B"+(installmentRow+1));
				}
				split = date.split("/");
				day = Integer.valueOf(split[0]);
				month = Integer.valueOf(split[1]);
				year = Integer.valueOf(split[2]);
				amount = (int) (workbook.getNumber(installmentRow, 2)*100 +0.0001); 
				String modeName = workbook.getText(installmentRow, 3);
				Mode mode = null;
				for(Mode current:api.getModeList())
					if(modeName.equals(current.getName()))
						mode = current;
				if(mode == null)
					mode = api.addMode(modeName);
				description = workbook.getText(installmentRow, 4);
				api.addLoanInstallment(name + " - Loan Installment", amount, day, month, year, description, mode, loan);
				
				installmentRow++;
			}
			
			//Loop until no more future payments
			while(workbook.getType(paymentRow, 6) != WorkBook.TypeEmpty){
				date = workbook.getFormattedText(paymentRow, 6);
				//
				System.out.println(date);
				//
				if(!date.matches("\\d*/\\d*/\\d*")){
					System.out.println("Invalid date format for Loan at G"+(paymentRow+1));
					throw new Exception("Invalid date format for Loan at G"+(paymentRow+1));
				}
				split = date.split("/");
				day = Integer.valueOf(split[0]);
				month = Integer.valueOf(split[1]);
				year = Integer.valueOf(split[2]);
				amount = (int) (workbook.getNumber(paymentRow, 7)*100 +0.0001); 
				loan.addFuturePayment(amount, day, month, year);
			}
			row = installmentRow>paymentRow ? installmentRow + 1 : paymentRow + 1;
			
		}
	}

}
